<?php
/**
 * Created by PhpStorm.
 * User: chenishr
 * Date: 04/03/2018
 * Time: 6:23 PM
 */

namespace app\index\model;

use think\Model;
use think\Db;

class Product extends Model {
    // 数据表主键
    protected $pk = 'prod_id';

    /**
     * 获取产品信息
     * @return mixed
     */
    public function getAllProducts(){
        $sql = "SELECT 
                p.prod_id, 
                min(p.name) name,
                COUNT(m.mail_id) mails
            FROM
                g_product p
                    LEFT JOIN
                g_mail m ON p.prod_id = m.prod_id
            GROUP BY p.prod_id";

        return DB::query($sql);
    }

    /**
     * 根据日期，返回产品的统计数据
     * @param $beginDate
     * @param $endDate
     */
    public function getProductStatic($beginDate,$endDate){
        $sql = "SELECT 
                prod.prod_id,
                min(prod.name) product,
                sum(case when 0 = mail.type then 1 else 0 end) type_0,
                sum(case when 1 = mail.type then 1 else 0 end) type_1,
                sum(case when 2 = mail.type then 1 else 0 end) type_2,
                sum(case when 3 = mail.type then 1 else 0 end) type_3,
                count(prod.prod_id) cnt
            FROM
                g_product prod
                    LEFT JOIN
                g_mail mail ON prod.prod_id = mail.prod_id
            where mail.mail_date >= '{$beginDate}' and mail.mail_date <= '{$endDate}'
            group by prod.prod_id";

        return Db::query($sql);
    }
}
